﻿#region License
/*
 *CopyRight (c) By Denuvead All Rights Reserved
 *CreateDate:
 *Info:
 */
#endregion
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Configuration;

namespace PriErp.Data
{
//<configuration>
//  <appSettings>
//    <add key="conStringName" value="SqlLiteConnection"/>
//  </appSettings>
//  <connectionStrings>
//    <!--<add name="defaultConnectionString1" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\UserDB.mdb;Jet OLEDB:Database Password=1234567" providerName="System.Data.OleDb"/>
//    <add name="defaultConnectionString2" connectionString="Data Source=10.152.86.42;Initial Catalog=UserDB;Persist Security Info=False;User ID=sa; pwd = 1234567" providerName="System.Data.SqlClient"/>-->
//    <add name="SqlLiteConnection" connectionString="Data Source=db\perp.db3;Version=3;BinaryGUID=False;" providerName="System.Data.SQLite"/>
//  </connectionStrings>
//  <system.data>
//    <DbProviderFactories>
//      <remove invariant="System.Data.SQLite"/>
//      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite"/>
//    </DbProviderFactories>
//  </system.data>
//</configuration>
    public static class DBHelper
    {
        private static readonly string connStringName = ConfigurationManager.AppSettings["connStringName"].ToString();
        private static readonly string connString = ConfigurationManager.ConnectionStrings[connStringName].ConnectionString;
        private static readonly string providerName = ConfigurationManager.ConnectionStrings[connStringName].ProviderName;
        public static DbProviderFactory provider = DbProviderFactories.GetFactory(providerName);

        #region Public Mothods

        public static int ExcuteNonQuery(string cmdText, params DbParameter[] cmdParms)
        {
            return ExcuteNonQuery(null, CommandType.Text, cmdText, cmdParms);
        }

        public static int ExcuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            return ExcuteNonQuery(null, cmdType, cmdText, cmdParms);
        }

        public static int ExcuteNonQuery(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (DbConnection conn = provider.CreateConnection())
            {
                using (DbCommand cmd = provider.CreateCommand())
                {
                    PrepareCommand(cmd, conn, trans, cmdType, cmdText, cmdParms);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public static DbDataReader ExecuteReader(string cmdText, params DbParameter[] cmdParms)
        {
            return ExecuteReader(null, CommandType.Text, cmdText, cmdParms);
        }

        public static DbDataReader ExecuteReader(CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            return ExecuteReader(null, cmdType, cmdText, cmdParms);
        }

        public static DbDataReader ExecuteReader(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            DbConnection conn = provider.CreateConnection();
            DbCommand cmd = provider.CreateCommand();

            try
            {
                PrepareCommand(cmd, conn, trans, cmdType, cmdText, cmdParms);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                conn.Close();
                cmd.Dispose();
                throw;
            }
        }

        public static object ExecuteScalar(string cmdText, params DbParameter[] cmdParms)
        {
            return ExecuteScalar(null, CommandType.Text, cmdText, cmdParms);
        }

        public static object ExecuteScalar(CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            return ExecuteScalar(null, cmdType, cmdText, cmdParms);
        }

        public static object ExecuteScalar(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (DbConnection conn = provider.CreateConnection())
            {
                using (DbCommand cmd = provider.CreateCommand())
                {
                    PrepareCommand(cmd, conn, trans, cmdType, cmdText, cmdParms);
                    return cmd.ExecuteScalar();
                }
            }
        }

        #endregion

        #region Private Methods

        private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType, string cmdText, DbParameter[] cmdParms)
        {
            conn.ConnectionString = connString;
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (DbParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
        #endregion
    }
}